using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using MySqlConnector;
using NLog;
using WifiSample.Model;

namespace MintService
{
    public class DapperHelper
    {
        private static DapperHelper _instance;
        private static IDbConnection connection;
        private static readonly object Padlock = new object();
        private Logger logger = LogManager.GetCurrentClassLogger();

        private DapperHelper()
        {
            Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
        }

        public static DapperHelper Instance
        {
            get
            {
                lock (Padlock)
                {
                    return _instance = _instance == null ? new DapperHelper() : _instance;
                }
            }
        }

        public IDbConnection Connection
        {
            get
            {
                string connectionString = "server=127.0.0.1;user=admin;database=wifi;port=3306;password=admin";
                var connection = new MySqlConnection(connectionString);
                return connection;
            }
        }

        public static IDbConnection MakeNewConnection()
        {
            string connectionString = "server=127.0.0.1;user=admin;database=wifi;port=3306;password=admin";
            var connection = new MySqlConnection(connectionString);
            connection.Open();
            return connection;
        }

        public static int Execute(string sql)
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql);
        }

        public static Password GetNext(string table, int idx)
        {
            if (connection == null) connection = Instance.Connection;
            string sql = $"select id,pwd,cnt,lasttime from {table} where id = {idx}";
            var passwords = connection.Query<Password>(sql);
            if (passwords.Any())
            {
                return passwords.First();
            }
            return new Password();
        }

        /// <summary>
        /// 添加.
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        /// </summary>
        public static int Add<T>(string sql, T t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql, t);
        }

        /// <summary>
        /// 批量添加.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>int.</returns>
        public static int Add<T>(string sql, List<T> t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql, t);
        }

        /// <summary>
        /// 删除.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        public static int Delete<T>(string sql, T t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql, t);
        }

        /// <summary>
        /// 批量删除.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>int.</returns>
        public static int Delete<T>(string sql, List<T> t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql, t);
        }

        /// <summary>
        /// 修改.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        public static int Update<T>(string sql, T t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql, t);
        }

        /// <summary>
        /// 批量修改.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>int.</returns>
        public static int Update<T>(string sql, List<T> t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Execute(sql, t);
        }

        /// <summary>
        /// 查询.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <returns>泛型类.</returns>
        public static List<T> Query<T>(string sql)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Query<T>(sql).ToList();
        }

        /// <summary>
        /// 查询指定数据.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>类.</returns>
        public static T Query<T>(string sql, T t)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Query<T>(sql, t).SingleOrDefault();
        }

        /// <summary>
        /// 查询的in操作.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <returns>泛型类.</returns>
        public static List<T> Query<T>(string sql, int[] ids)
            where T : class
        {
            if (connection == null) connection = Instance.Connection;
            return connection.Query<T>(sql, new { ids }).ToList();
        }

        public static dynamic QuerySingle<T>(string sql, T t) where T : class
        {
            if (connection == null) connection = Instance.Connection;
            dynamic result = null;
            return connection.QuerySingle(sql, t);
        }

        public bool UpdateCache(string cacheKey, string cacheValue)
        {
            bool bResult = false;
            var sql = "INSERT INTO ht_cache (cache_key, cache_value) VALUES (@CacheKey, @CacheValue) ON DUPLICATE KEY UPDATE cache_value = @CacheValue";
            try
            {
                Instance.Connection.Execute(sql, new { CacheKey = cacheKey, CacheValue = cacheValue });
                bResult = true;
            }
            catch (Exception ex)
            {
                logger.Warn($"Query Error {ex.Message}");
            }
            return bResult;
        }

        public string GetCacheValue(string cacheKey)
        {
            var sql = "SELECT cache_value as CacheValue FROM ht_cache WHERE cache_key = @CacheKey";
            try
            {
                var dbResult = Instance.Connection.Query(sql, new { CacheKey = cacheKey });
                if (dbResult.Any())
                {
                    return dbResult.First().CacheValue;
                }
                return null;
            }
            catch (Exception ex)
            {
                logger.Warn($"Query Error {ex.Message}");
                return null;
            }
        }

        public void UpdateGKDataStatus(string seqNumber, string fOrE, int status)
        {
            var columnName = fOrE == "F" ? "gk_f_status" : "gk_e_status";
            try
            {
                var sql = $"UPDATE ht_mint_status SET {columnName} = {status} WHERE seq_number = @SeqNumber";
                DapperHelper.Instance.Connection.Query(sql, new
                {
                    SeqNumber = seqNumber
                });
            }
            catch (Exception ex)
            {
                logger.Warn($"UpdateGKDataStatus {ex.Message}");
            }
        }

        public void UpdateQRDataStatus(string seqNumber, string fOrE, int status)
        {
            var columnName = fOrE == "F" ? "qr_f_status" : "qr_e_status";
            try
            {
                var sql = $"UPDATE ht_mint_status SET {columnName} = {status} WHERE seq_number = @SeqNumber";
                DapperHelper.Instance.Connection.Query(sql, new
                {
                    SeqNumber = seqNumber
                });
            }
            catch (Exception ex)
            {
                logger.Warn($"UpdateGKDataStatus {ex.Message}");
            }
        }
        /// <summary>
        /// 判断管壳打印状态，0为未打印，1为已打印，2为数据不存在
        /// </summary>
        /// <param name="seqNumber"></param>
        /// <param name="fOrE"></param>
        /// <returns></returns>
        public int CanMintGK(string seqNumber, string fOrE)
        {
            int bResult = 4;
            try
            {
                var sql = $"SELECT * FROM ht_mint_status WHERE seq_number = @SeqNumber";
                var dbResult = Instance.Connection.Query(sql, new
                {
                    SeqNumber = seqNumber
                });
                if (dbResult.Any())
                {
                    if (fOrE == "F")
                    {
                        bResult = dbResult.First().gk_f_status;
                    }
                    else
                    {
                        bResult = dbResult.First().gk_e_status;
                    }
                }
                else
                {
                    bResult = 3;
                }
            }
            catch (Exception ex)
            {
                logger.Warn($"CanMintGK {ex.Message}", true);
            }
            return bResult;
        }
        /// <summary>
        /// 判断线夹打印状态，0为未打印，1为已打印，2为数据不存在
        /// </summary>
        /// <param name="seqNumber"></param>
        /// <param name="fOrE"></param>
        /// <returns></returns>
        public int CanMintXJ(string seqNumber, string fOrE)
        {
            int bResult = 4;
            try
            {
                var sql = $"SELECT * FROM ht_mint_status WHERE seq_number = @SeqNumber";
                var dbResult = Instance.Connection.Query(sql, new
                {
                    SeqNumber = seqNumber
                });
                if (dbResult.Any())
                {
                    if (fOrE == "F")
                    {
                        bResult = dbResult.First().qr_f_status;
                    }
                    else
                    {
                        bResult = dbResult.First().qr_e_status;
                    }
                }
                else
                {
                    bResult = 3;
                }
            }
            catch (Exception ex)
            {
                logger.Warn($"CanMintXJ {ex.Message}", true);
            }
            return bResult;
        }
    }
}